package com.msg.test02;

import java.sql.*;
import java.util.ArrayList;
import java.util.Scanner;

/**
 * @author wuJiaWei
 * @version 1.0
 * 【老马书城数据库版本】项目功能：
 * 1.展示书籍
 * 2.上线书籍
 * 3.下架书籍
 * 4.退出应用
 */
public class Test {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        int choice = 0;
        //借助Scanner类获取用户输入
        Scanner sc = new Scanner(System.in);
        while (choice != 4) {
            //打印菜单
            System.out.println("【老马书城集合版】");
            System.out.println("1.查询书籍");
            System.out.println("2.查询所有书籍信息");
            System.out.println("3.删除书籍");
            System.out.println("4.退出应用");
            System.out.println("请选择操作：");
            //利用键盘录入序号
            choice = sc.nextInt();
            //根据用户输入的序号执行不同的操作
            switch (choice){
                case 1:
                    System.out.println("输入书籍编号:");
                    int bno = sc.nextInt();
                    //调用方法查询书籍
                    Book book = findBookById(bno);
                    if (book != null) {
                        System.out.println("书籍名称为："+book.getName());
                    } else {
                        System.out.println("没有找到该书籍");
                    }
                    break;
                case 2:
                    System.out.println("展示所有书籍----------");
                    ArrayList books = findAllBooks();
                    if (books.size() ==0){
                        System.out.println("没有书籍");
                    }else {
                        for (Object b : books){
                            Book book1 = (Book) b;
                            System.out.println("书籍编号："+book1.getId()+" 书籍名称："+book1.getName()+" 书籍价格："+book1.getPrice());
                        }
                    }
                    break;
                case 3:
                    System.out.println("删除书籍---------");
                    System.out.println("请输入要删除书籍的编号：");
                    int deleteNo = sc.nextInt();
                    int n = deleteBook(deleteNo);
                    if (n>0){
                        System.out.println("删除成功");
                    }else {
                        System.out.println("删除失败");
                    }
                    break;
                case 4:
                    System.out.println("退出应用-------");
                    break;
                default:
                    System.out.println("输入有误，请重新输入");
            }
        }
    }

    //根据编号查询对应的书籍
    public static Book findBookById(int bno) throws ClassNotFoundException, SQLException {
        String sql = "select * from t_book where id = " + bno;
        //加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/msb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //创建会话
        Statement stmt = conn.createStatement();
        //执行sql
        //发送SQL:RestSet结果集合 - 结果集
        ResultSet rs = stmt.executeQuery(sql);
        //处理结果集
        Book book = null;
        while (rs.next()) {//判断是否有记录存在
            //先将结果做接收
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String author = rs.getString("author");
            double price = rs.getDouble("price");
            book = new Book(id, name, author, price);
        }
        //关闭资源
        rs.close();
        stmt.close();
        conn.close();
        return book;
    }

    /**
     * 查询全部书籍
     * @return
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static ArrayList findAllBooks() throws ClassNotFoundException, SQLException {
        //定义集合
        ArrayList books = new ArrayList();
        String sql = "select * from t_book";
        //加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/msb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //创建会话
        Statement stmt = conn.createStatement();
        //执行sql
        //发送SQL:RestSet结果集合 - 结果集
        ResultSet rs = stmt.executeQuery(sql);
        //处理结果集
        Book book = null;
        while (rs.next()) {//判断是否有记录存在
            //先将结果做接收
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String author = rs.getString("author");
            double price = rs.getDouble("price");
            //将结果封装到对象中
            book = new Book(id, name, author, price);
            //将对象添加到集合中
            books.add(book);
        }
        //关闭资源
        rs.close();
        stmt.close();
        conn.close();
        return books;
    }
    //删除指定编号的书籍
    public static int deleteBook(int id) throws ClassNotFoundException, SQLException {
        String sql = "delete from t_book where id = " + id;
        //加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接
        String url = "jdbc:mysql://127.0.0.1:3306/msb?useSSL=false&useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        Connection conn = DriverManager.getConnection(url, username, password);
        //创建会话
        Statement stmt = conn.createStatement();
        //执行sql
        int n = stmt.executeUpdate(sql);
        //关闭资源
        stmt.close();
        conn.close();

        return n;
    }
}
